
library(tidyverse)
library(lubridate)

conn <- src_sqlite("~/Documents/rockontrol/合肥项目数据/合肥市项目.db") 

day_this <- "2019-08-20" 

day_same <- day_this %>% ymd() - years(1)
 
last_floor_year <- day_same %>% ymd() %>% floor_date(unit = "year") %>% as.character() 

town <- "包河区" 

dataset <- conn %>% tbl("日数据") %>%
  inner_join(tbl(conn,"站点信息"),by  =c("站点名称"="站点")) %>%
  filter(类型 == "国控站",区县== town,日期 <= day_this,日期>= last_floor_year)  %>% collect()  


compute_index<- function(data){
  temp <- data %>% group_by(站点名称) %>% summarise(SO2浓度 = mean(SO2浓度,na.rm = TRUE),
                             PM2.5浓度 = mean(PM2.5浓度,na.rm = TRUE),
                             PM10浓度 = mean(PM10浓度,na.rm = TRUE),
                             NO2浓度 = mean(NO2浓度,na.rm = TRUE),
                             CO浓度 = quantile(CO浓度,0.95,na.rm=TRUE),
                             O3浓度= quantile(O3浓度,0.9,na.rm=TRUE))
  res<- temp %>% mutate (  综合指数 = round( SO2浓度*100/60)/100+
                               round(PM2.5浓度*100/35)/100+
                               round(PM10浓度*100/70)/100+
                               round(NO2浓度*100/40)/100+
                               round(CO浓度*100/4)/100+
                               round(O3浓度*100/160)/100,
                   SO2浓度 = round(SO2浓度*10)/10,
                   PM2.5浓度 = round(PM2.5浓度*10)/10,
                   PM10浓度 = round(PM10浓度*10)/10,
                   NO2浓度 = round(NO2浓度*10)/10,
                  CO浓度 = round(CO浓度*10)/10,
                   O3浓度 = round(O3浓度*10)/10);
  res <- data %>% filter(等级 %in% c("优","良")) %>% 
    group_by(站点名称) %>% 
   count()%>%select(站点名称,达标天数 = n) %>%inner_join(res,by = "站点名称");
    return(res);
  }


dataset <- dataset %>% mutate(日期 = ymd(日期))

day_this <- ymd(day_this)

year_this <- dataset %>% filter(year(日期) == year(day_this)) %>% compute_index() %>% mutate(年份= paste0(year(day_this),"年"))
year_last <- dataset %>% filter(year(日期) == year(day_same)) %>% compute_index() %>% mutate(年份= paste0(year(day_same),"年"))
year_last_now <-  dataset %>% filter(year(日期) == year(day_same),日期 <= day_same) %>% compute_index() %>% mutate(年份= paste0(floor_date(day_same,unit="year"),"至",day_same))

month_this <- dataset %>% filter(year(日期) == year(day_this),month(日期) == month(day_this)) %>% compute_index() %>% mutate(年份 = paste0(floor_date(day_this,unit="month"),"至",day_this))
month_last_now  <- dataset %>% filter(year(日期) == year(day_same),month(日期) == month(day_same),日期 <= day_same) %>% compute_index()%>%mutate(年份 = paste0(floor_date(day_same,unit="month"),"至",day_same))
month_last  <- dataset %>% filter(year(日期) == year(day_same),month(日期) == month(day_same)) %>% compute_index()%>%mutate(年份 = paste0(year(day_same),"年",month(day_same),"月"))

 year_this %>%
   union_all(year_last) %>%
   union_all(year_last_now) %>% 
   union_all(month_this) %>%
   union_all(month_last_now) %>%
   union_all(month_last) %>%write_csv("~/Documents/res.csv")

